﻿using CallCenter.Data.Base;
using CallCenter.Data.BizUtilities.Base;
using CallCenter.Data.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;


namespace CallCenter.Data.BizUtilities
{
    public class ServiceContractUtility : BaseUtility
    {
        public ServiceContractUtility(DataManager manager)
            : base(manager)
        { }

        public ReturnMessage<ServiceContractViewModel> InsertToServiceContract(ServiceContractViewModel model)
        {
            var returnMessage = new ReturnMessage<ServiceContractViewModel>();
            //  if(this.ValidateScratchPadModel(model))
            //   {
      
            model.contractorId = 1;
          
            if (String.IsNullOrEmpty(model.note))
                model.note = "";
            var id = this.Insert(model.companyId, model.productRequestId, 1, model.cost, model.hourlyRate, model.startDate, model.endDate, model.hoursPerMonth, model.renewalDate, "", "Open", model.note);
            if (id == -1)
            {
                returnMessage.Message = "We cannot insert data to database";
                returnMessage.Status = false;
            }
            else
            {
                returnMessage.Message = "Record has been inserted  to database";
                model.id = id;
                returnMessage.Status = true;
            }
            return returnMessage;
        }

        public ReturnMessage<ServiceContractViewModel> UpdateToAddress(ServiceContractViewModel model)
        {
            var returnMessage = new ReturnMessage<ServiceContractViewModel>();
            //  if(this.ValidateScratchPadModel(model))
            //   {
            model.contractorId = 1;
         

            if (String.IsNullOrEmpty(model.note))
                model.note = "";
            var id = this.Update(model.id,model.companyId, model.productRequestId, model.contractorId, model.cost, model.hourlyRate, model.startDate, model.endDate, model.hoursPerMonth, model.renewalDate, "", "Open", model.note);
            if (id == -1)
            {
                returnMessage.Message = "We cannot insert data to database";
                returnMessage.Status = false;
            }
            else
            {

                returnMessage.Message = "Record has been updated  to database";
                model.id = id;
                returnMessage.Status = true;
            }
            return returnMessage;
        }

        public int Insert( //define placeholders for the values in the table to insert
           int companyId,
           int productRequestId,
           int contractorId,
           decimal cost,
           decimal hourlyRate,
           Nullable<DateTime> startDate,
           Nullable<DateTime> endDate,
           decimal hoursPerMonth,
           Nullable<DateTime> renewalDate,
           string contractLetter,
           string contractStatus,
           string note
         )
        {

            int nRtn = -1;
 string sql  = "Insert INTO ddlServiceContracts " +
                               "(companyId, productRequestId, contractorId, cost, hourlyRate, startDate, endDate, hoursPerMonth, " +
                               "renewalDate, contractLetter, contractStatus, note) " +
                               "VALUES(@companyId, @productRequestId, @contractorId, @cost, @hourlyRate, @startDate, @endDate," +
                               " @hoursPerMonths, @renewalDate, @contractLetter, @contractStatus, @notes) " +
                               "SELECT @pResult =@@IDENTITY";

            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@companyId", SqlDbType.Int, 10, companyId);
                this.Factory.AddParameter(cmd, "@productRequestId", SqlDbType.Int, 100, productRequestId);
                this.Factory.AddParameter(cmd, "@contractorId", SqlDbType.Int, 10, contractorId);
                this.Factory.AddParameter(cmd, "@cost", SqlDbType.Decimal, 10, cost);
                this.Factory.AddParameter(cmd, "@hourlyRate", SqlDbType.Decimal, 10, hourlyRate);
                //this.Factory.AddParameter(cmd, "@startDate", SqlDbType.SmallDateTime, 100, startDate);
                //this.Factory.AddParameter(cmd, "@endDate", SqlDbType.SmallDateTime, 10, endDate);
                this.Factory.AddParameter(cmd, "@hoursPerMonths", SqlDbType.Int, 10, hoursPerMonth);
               // this.Factory.AddParameter(cmd, "@renewalDate", SqlDbType.SmallDateTime, 10, renewalDate);
                this.Factory.AddParameter(cmd, "@contractLetter", SqlDbType.NVarChar, 10, contractLetter);
                this.Factory.AddParameter(cmd, "@contractStatus", SqlDbType.NVarChar, 100, contractStatus);
                this.Factory.AddParameter(cmd, "@notes", SqlDbType.NVarChar, 1000, note);
                
                var param = this.Factory.AddParameter(cmd, "@pResult", System.Data.SqlDbType.NVarChar, 500, null);
                param.Direction = System.Data.ParameterDirection.Output;


                if ((startDate == null) || (startDate.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@startDate", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@startDate", SqlDbType.DateTime, 10, startDate);


                if ((endDate == null) || (endDate.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@endDate", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@endDate", SqlDbType.DateTime, 10, endDate);

                if ((renewalDate == null) || (renewalDate.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@renewalDate", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@renewalDate", SqlDbType.DateTime, 10, renewalDate);

                this.Factory.ExecuteNonQuery(cmd);
                nRtn = Convert.ToInt32(param.Value);
            }
            return nRtn;



           
        }


        public int Update( //define placeholders for the values in the table to insert
          int id,
          int companyId,
          int productRequestId,
          int contractorId,
          decimal cost,
          decimal hourlyRate,
         Nullable<DateTime> startDate,
         Nullable<DateTime> endDate,
          decimal hoursPerMonth,
         Nullable<DateTime> renewalDate,
          string contractLetter,
          string contractStatus,
          string note
        )
        {

            int nRtn = -1;

       string sql= "UPDATE ddlServiceContracts SET " +
                               " companyId=@companyId, productRequestId=@productRequestId, contractorId=@contractorId, cost=@cost, hourlyRate=@hourlyRate," +
                               " startDate= @startDate, endDate=@endDate, hoursPerMonth=@hoursPerMonth, " +
                               " renewalDate=@renewalDate, contractLetter=@contractLetter, contractStatus=@contractStatus, note= @note WHERE id=@id ";


            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@companyId", SqlDbType.Int, 10, companyId);
                this.Factory.AddParameter(cmd, "@productRequestId", SqlDbType.Int, 100, productRequestId);
                this.Factory.AddParameter(cmd, "@contractorId", SqlDbType.Int, 10, contractorId);
                this.Factory.AddParameter(cmd, "@cost", SqlDbType.Int, 10, cost);
                this.Factory.AddParameter(cmd, "@hourlyRate", SqlDbType.Int, 10, hourlyRate);
                this.Factory.AddParameter(cmd, "@startDate", SqlDbType.SmallDateTime, 100, startDate);
                this.Factory.AddParameter(cmd, "@endDate", SqlDbType.SmallDateTime, 10, endDate);
                this.Factory.AddParameter(cmd, "@hoursPerMonth", SqlDbType.Int, 10, hoursPerMonth);
                this.Factory.AddParameter(cmd, "@renewalDate", SqlDbType.SmallDateTime, 10, renewalDate);
                this.Factory.AddParameter(cmd, "@contractLetter", SqlDbType.NVarChar, 10, contractLetter);
                this.Factory.AddParameter(cmd, "@contractStatus", SqlDbType.NVarChar, 100, contractStatus);
                this.Factory.AddParameter(cmd, "@note", SqlDbType.NVarChar, 100, note);

                this.Factory.AddParameter(cmd, "@id", SqlDbType.Int, 10, id);

                if ((startDate == null) || (startDate.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@startDate", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@startDate", SqlDbType.DateTime, 10, startDate);


                if ((endDate == null) || (endDate.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@endDate", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@endDate", SqlDbType.DateTime, 10, endDate);

                if ((renewalDate == null) || (renewalDate.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@renewalDate", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@renewalDate", SqlDbType.DateTime, 10, renewalDate);
                this.Factory.ExecuteNonQuery(cmd);

            }
            nRtn = id;
            return nRtn; //retruning the data stored in the table
        }
        public List<ServiceContractViewModel> GetAllProductRequestContract(int productRequestId)
        {
            string sql = "SELECT * ,cast(sc.cost as decimal(18,3)) FROM ddlServiceContracts sc " +
                                      //"LEFT JOIN ddlProducts p ON p.id = sc.productId " +
                                      " WHERE productRequestId = " + productRequestId;
            return this.Factory.ExecuteObjects<ServiceContractViewModel>(sql);

        }

        public void Delete(int nContactID) //delete the row of data using the id passed to the function
        {
           string sql= "DELETE FROM ddlServiceContracts " +
                "WHERE ID = @ID ";

            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@ID", SqlDbType.Int, 10, nContactID);
                this.Factory.ExecuteNonQuery(cmd);
            }
        }

        public void DeleteAll() //delete all the rows in the data.
        {
          string sql= "DELETE FROM ddlServiceContracts ";
            this.Factory.ExecuteTable(sql);
        }
    }
}
